package com.itany.jdbc;

import com.itany.rowmapper.UserRowMapper;
import com.itany.util.JDBCTemplate;
import com.itany.util.KeyHolder;
import com.itany.util.PreparedStatementCreator;
import com.itany.util.RowMapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

/**
 * @Author:石小俊
 * @Date:2022/11/14 16:51
 * @Version:1.0-SNAPSHOT
 * @Description:
 */
public class Test15 {

    private static JDBCTemplate jdbcTemplate = new JDBCTemplate();

    public static void main(String[] args) {
//        User user = new User("孙尚香","666666","13612345678","王者荣耀");
//        insertUser(user);
//        User user = new User(4,"吕布","666666","13612345678","王者荣耀");
//        update(user);

//        deleteById(3);
//        List<User> users = selectAll();
//        for(User user : users){
//            System.out.println(user);
//        }

//        User user = selectById(1);
//        System.out.println(user);

//        User user = selectByUsernameAndPassword("admin","123456");
//        System.out.println(user);

        User user = new User("tom","666666","13612345678","江苏-南京");
        Integer id = insertReturnPrimaryKey(user);
        System.out.println("返回的主键值为:"+id);
    }

    public static Integer insertReturnPrimaryKey(User user){
        KeyHolder keyHolder = new KeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws Exception {
                String sql = new StringBuffer()
                        .append(" insert into ")
                        .append(" t_user ")
                        .append("   (username,password,phone,address)")
                        .append(" values")
                        .append("   (?,?,?,?)")
                        .toString();
                PreparedStatement ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1,user.getUsername());
                ps.setString(2,user.getPassword());
                ps.setString(3,user.getPhone());
                ps.setString(4,user.getAddress());
                return ps;
            }
        },keyHolder);
        return keyHolder.getKey();
    }


    public static User selectByUsernameAndPassword(String username,String password){
        String sql = new StringBuffer()
                .append(" select id,username,password,phone,address ")
                .append(" from t_user")
                .append(" where username = ?  ")
                .append(" and password = ? ")
                .toString();
        List<User> users = jdbcTemplate.query(sql,new UserRowMapper(),username,password);
        return users.isEmpty() ? null : users.get(0);
    }

    public static User selectById(Integer id){
        String sql = new StringBuffer()
                .append(" select id,username,password,phone,address ")
                .append(" from t_user")
                .append(" where id = ? ")
                .toString();
        List<User> users = jdbcTemplate.query(sql,new UserRowMapper(),id);
//        if(users.isEmpty()){
//            return null;
//        }else{
//            return users.get(0);
//        }
        return users.isEmpty() ? null : users.get(0);
    }

    public static List<User> selectAll(){
        String sql = new StringBuffer()
                .append(" select id,username,password,phone,address ")
                .append(" from t_user")
                .toString();
//        return jdbcTemplate.query(sql, new RowMapper() {
//            @Override
//            public Object mapRow(ResultSet rs) throws Exception {
//                User user = new User();
//                user.setId(rs.getInt("id"));
//                user.setUsername(rs.getString("username"));
//                user.setPassword(rs.getString("password"));
//                user.setPhone(rs.getString("phone"));
//                user.setAddress(rs.getString("address"));
//                return user;
//            }
//        });
        return jdbcTemplate.query(sql,new UserRowMapper());
    }

    public static void insertUser(User user){
        String sql = new StringBuffer()
                .append(" insert into ")
                .append(" t_user ")
                .append("   (username,password,phone,address)")
                .append(" values ")
                .append("   (?,?,?,?)")
                .toString();
        jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getPhone(),user.getAddress());
        System.out.println("添加成功");
    }

    public static void deleteById(Integer id){
        String sql = new StringBuffer()
                .append(" delete from t_user ")
                .append(" where id = ? ")
                .toString();
        jdbcTemplate.update(sql,id);
        System.out.println("删除成功");
    }

    public static void update(User user){
        String sql = new StringBuffer()
                .append(" update t_user ")
                .append(" set ")
                .append("   username = ?, ")
                .append("   password = ?, ")
                .append("   phone = ?, ")
                .append("   address = ? ")
                .append(" where id = ? ")
                .toString();
        jdbcTemplate.update(sql,user.getUsername(),user.getPassword(),user.getPhone(),user.getAddress(),user.getId());
        System.out.println("修改成功");
    }

}
